package com.situ.dao;

import com.situ.entity.Product;
import com.situ.util.DBUtil;
import com.situ.util.StringUtil;
import com.situ.vo.ProductVO;

import java.util.List;

/**
 * @author yangkx
 * @create 2023-08-28-15:22
 */
public class ProductDao {

    //查询表格数据
    public List<ProductVO> list(Integer page, Integer limit, String name, String manufacturer){
        String sql = "select store.*,category.`name` 'categoryName' from store\n" +
                "left join category on category.id=store.category_id";
        //判断
        //name非空 且 manufacturer非空
        if (StringUtil.isNotBlank(name) && StringUtil.isNotBlank(manufacturer)){
            sql += " where instr(store.name,?)>0 and instr(manufacturer,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,ProductVO.class,name,manufacturer,(page-1)*limit,limit);
        }
        //name非空
        if(StringUtil.isNotBlank(name)){
            sql += " where instr(store.name,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,ProductVO.class,name,(page-1)*limit,limit);
        }
        //manufacturer非空
        if (StringUtil.isNotBlank(manufacturer)){
            sql += " where instr(manufacturer,?)>0 limit ?,?";
            return DBUtil.executeDQL(sql,ProductVO.class,manufacturer,(page-1)*limit,limit);
        }
        //三个if都不通过，则name 和 manufacturer都为空，查全部
        sql += " limit ?,?";
        return DBUtil.executeDQL(sql,ProductVO.class,(page-1)*limit,limit);
    }

    //保存
    public void save(Product p) {
        String sql = "insert into store(category_id,name,manufacturer,model,specification) value(?,?,?,?,?)";
        DBUtil.executeDML(sql,p.getCategoryId(),p.getName(),p.getManufacturer(),p.getModel(),p.getSpecification());
    }

    //更新
    public void change(Product p) {
        String sql = "update store set category_id=?,name=?,manufacturer=?,model=?,specification=? where id=?";
        DBUtil.executeDML(sql,p.getCategoryId(),p.getName(),p.getManufacturer(),p.getModel(),p.getSpecification(),p.getId());
    }

    //删除
    public void delete(Integer id) {
        String sql = "delete from store where id = ?";
        DBUtil.executeDML(sql,id);
    }

    //编辑——根据ID查询该行数据带到前台
    public List<Product> selectById(Integer id) {
        String sql = "select * from store where id = ?";
        return DBUtil.executeDQL(sql,Product.class,id);
    }


    //统计查询结果——根据过滤条件帮我们统计一个Long值 来查询结果表示共有多少条数据
    public Long count(String name, String manufacturer) {
        String sql = "select count(1) from store";
        //name非空 且 manufacturer非空
        if (StringUtil.isNotBlank(name) && StringUtil.isNotBlank(manufacturer)){
            sql += "  where instr(name,?)>0 and instr(manufacturer,?)>0";
            return DBUtil.executeCount(sql,name,manufacturer);
        }
        //name非空
        if(StringUtil.isNotBlank(name)){
            sql += "  where instr(name,?)>0";
            return DBUtil.executeCount(sql,name);
        }
        //manufacturer非空
        if (StringUtil.isNotBlank(manufacturer)){
            sql += "  where instr(manufacturer,?)>0";
            return DBUtil.executeCount(sql,manufacturer);
        }
        return DBUtil.executeCount(sql);

    }
}
